// 查询记录
const { getWhereStr } = require("./_utils");
const { N, needParam, splitModChar, splitAliasChar, consoleLog, typeOf } = require("../../utils");
const { getSomeFieldNames, getStandFieldNames } = require("../../compile/mysql/_utils");
const { toStandardSqlField } = require("../../compile/mysql/_utils");
const { standTables } = require("../../../../../app/tables-data");
const { getTablesSelectSql } = require("./_utils/tablesSelect");

/**
 * 获取JOIN表的字符串
 * @param {string} mainKey 主表的主键，例：user.id
 * @param {object} joinTableMap 要连接表的映射，例： { order: "user_id" }
 * @notice 左连接返回左表中的所有行以及右表中满足连接条件的行；右连接返回右表中的所有行以及左表中满足连接条件的行（简记：左链接优先满足左表，右链接优先满足右表）
 */
// function getJoinTableStr(joinTableMap) {
//   if (!joinTableMap) return "";
//   const joinTableNames = Object.keys(joinTableMap);
//   if (!joinTableNames.length) return "";
//   // modName 带修饰符的表单名称
//   return joinTableNames
//     .map(modName => {
//       const [tableNameStr, joinType = "LEFT"] = modName.split(splitModChar);
//       const [tName, aName = ""] = tableNameStr.split(splitAliasChar);
//       const equalStr = joinTableMap[modName];
//       return `${joinType.toUpperCase()} JOIN ${tName} ${aName} ON ${equalStr}`;
//     })
//     .join(N);
// }

/**
 * 获取orderBy的sql语句
 * @param {string[]} order orderBy的规则，例：[ "update_time|ASC", "create_time|DESC" ]，默认按DESC（降序）排序
 * @returns {string}
 */
function getOrderByStr(order, allFieldNames = []) {
  if (!order?.length) return "";
  const t = typeOf(order);
  if (t === "Array") {
    return order
      .map(key => {
        const [prop, mod = "DESC"] = key.split("|");
        if (allFieldNames?.length && !allFieldNames.includes(prop)) return "";
        return `${prop} ${mod.toUpperCase()}`;
      })
      .filter(it => !!it)
      .join(", ");
  } else {
    throw new Error(`暂未处理此类型：${t}`);
  }
}

/**
 * 获取单、多表查询的sql语句
 * @param {object} table 表名称或多表数据 例：'user'或[{ user: ["id", "name"]}, {order: ["user_id", "order_id", "amount"] }]
 * @param {string|boolean|array|object} where where规则
 * @notice 在上述映射结构中，第一个表为主表，其他为从表。主表第一个字段为主键，从表第一个字段为join主键，且这两个键都是必须的
 */
// 使用示例：
// 单表查询
// selectTable({user: undefined}, { user_name: "李四", age|>=: "20" });
// selectTable({user: ['*']}, { user_name: ["李四", "张三"] });
// 模糊查询
// selectTable({user: ['*']}, { user_name|like: "%四%"}); // 名字中含有四
// 正则表达式查询
// selectTable({user: ['*']}, { user_name|regexp: "^李.+" }); // 姓李的
// 取反查询
// selectTable({user: ['*']}, [{ "user_name|not": ["李四", "张三"], "age|!=": "24" }, { user_name: "王五" }]);
// 多表查询：order: 默认 LEFT 连接，log 指定为INNER连接，不会查出log表的字段，会查出price表的所有字段（除关联的主键外）
// selectTable({ user: ["id", "user_name", "nickname", "age"], order: ["user_id", "price", "amount"], "log|inner": ["user_id"], "price": ["user_id", "*"] }, { "user.age|>": "20", "order.price|>=": "20" });
// ASC 升序（从小到大），DESC 降序（从大到小）。
// order = [ "update_time|ASC", "create_time|DESC" ] 默认DESC排序
// limits = [0, 20]
exports.selectTable = function (table = needParam(), { where, order, limits, group, having }) {
  const isSingle = typeof table === "string" || table.length === 1; // 是否是单表查询
  const nChar = isSingle ? " " : N;
  const mainName = typeof table === "string" ? table : Object.keys(table[0])[0].split(splitAliasChar)[0];
  const mainFieldNames = getStandFieldNames(getStandFieldNames(["*"], mainName), mainName);
  const tableNames = isSingle ? [] : table.map(it => Object.keys(it)[0].split(splitAliasChar)[0]);
  sql = getTablesSelectSql(table);

  // WHERE
  const whereStr = getWhereStr(where, mainFieldNames, isSingle ? undefined : mainName, nChar);
  if (whereStr) sql += ` WHERE ${whereStr}`;

  // GROUP BY
  if (group?.length) {
    sql += ` GROUP BY ${group.join(",")}`;
  } else if (tableNames.length > 1) {
    sql += ` GROUP BY ${mainName}.id`;
  }

  // HAVING
  const havStr = getWhereStr(having, mainFieldNames, isSingle ? undefined : mainName, nChar);
  if (havStr) sql += ` HAVING ${havStr}`;

  // ORDER BY
  const orderStr = getOrderByStr(order, mainFieldNames);
  if (orderStr) sql += ` ORDER BY ${orderStr}`;
  if (limits?.length) sql += ` LIMIT ${limits.join(", ")}`;

  return `${sql};`;
};
